﻿Option Explicit On
Option Strict On

Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Imports FRAMEWORK
Imports System.Drawing.Drawing2D
Public Class frm_Customer
    Inherits asWins.Forms.BaseForm

    'dgvDetail.CommitEdit(DataGridViewDataErrorContexts.Commit);
    Dim Da As New FRAMEWORK.SQLDataAccess
    Dim oldValue As String = Nothing
    Dim newValue As String = Nothing

#Region "Private Members"
    Private colBtnDelete As New DataGridViewButtonColumn()
    'Private arrOptionColumn1 As New List(Of String)
    'Private arrOptionColumn2 As New List(Of String)
    'Private arrOptionColumn3 As New List(Of String)
    Private WithEvents mobjInnerTextBox As TextBox
    Private mboolNonNumberEntered As Boolean
    Private CusAddressIndex As String
    Private CusContactIndex As String
#End Region

#Region " Function and Procedure "
    Protected Overrides Sub OnPaint(ByVal e As System.Windows.Forms.PaintEventArgs)
        Dim lgb As New LinearGradientBrush(ClientRectangle, Color.Honeydew, Color.Aquamarine, LinearGradientMode.Vertical)
        e.Graphics.FillRectangle(lgb, ClientRectangle)

        lgb.Dispose()
    End Sub
    Private Sub Handler()
        ControlGotFocus(Me, Me)
        ControlLostFocus(Me, Me)
    End Sub
    Private Sub ControlGotFocus(ByVal parentCtr As Control, Optional ByVal ignoreControl As Control = Nothing)
        Dim ctr As Control
        For Each ctr In parentCtr.Controls
            If Not (ctr Is ignoreControl) Then
                If TypeOf ctr Is TextBox Then
                    AddHandler ctr.GotFocus, AddressOf EvenHand.KeyEventHand.TextBox_GotFocus
                ElseIf TypeOf ctr Is ComboBox Then
                    AddHandler DirectCast(ctr, ComboBox).GotFocus, AddressOf EvenHand.KeyEventHand.ComboBox_GotFocus
                End If
            End If
            ControlGotFocus(ctr, ignoreControl)
        Next
    End Sub
    Private Sub ControlLostFocus(ByVal parentCtr As Control, Optional ByVal ignoreControl As Control = Nothing)
        Dim ctr As Control
        For Each ctr In parentCtr.Controls
            If Not (ctr Is ignoreControl) Then
                If TypeOf ctr Is TextBox Then
                    AddHandler ctr.LostFocus, AddressOf EvenHand.KeyEventHand.TextBox_LostFocus
                ElseIf TypeOf ctr Is ComboBox Then
                    AddHandler DirectCast(ctr, ComboBox).LostFocus, AddressOf EvenHand.KeyEventHand.ComboBox_LostFocus
                End If
            End If
            ControlLostFocus(ctr, ignoreControl)
        Next
    End Sub
#End Region
    Private Sub frm_Customer_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        GetEmployee()
        LoadCustomer()

        Handler()
        GroupBox1.BackColor = Color.FromArgb(0, 0, 0, 0)
        GroupBox2.BackColor = Color.FromArgb(0, 0, 0, 0)
        GroupBox3.BackColor = Color.FromArgb(0, 0, 0, 0)
        dgvAddress.AutoGenerateColumns = False
        dgvContact.AutoGenerateColumns = False
        Dim FD As New FormFader
        FD.FadeIn(Me, 0.25)

    End Sub
    Private Sub LoadCustomer()
        Dim dt As DataTable

        Dim para(15) As SqlParameter

        para(0) = New SqlParameter("@ACTION", 3)
        para(1) = New SqlParameter("@Customer_Code", "")
        para(2) = New SqlParameter("@Employee_Code", "")
        para(3) = New SqlParameter("@Customer_Type", 0)
        para(4) = New SqlParameter("@Customer_Title", "")
        para(5) = New SqlParameter("@Customer_Name_1", "")
        para(6) = New SqlParameter("@Customer_Name_2", "")
        para(7) = New SqlParameter("@Customer_Dest", "")
        para(8) = New SqlParameter("@Customer_Status", 0)
        para(9) = New SqlParameter("@Create_User", FRAMEWORK.Framework.F_Username_Str)
        para(10) = New SqlParameter("@Create_Date", "")
        para(11) = New SqlParameter("@Update_User", FRAMEWORK.Framework.F_Username_Str)
        para(12) = New SqlParameter("@Update_Date", 0)
        para(13) = New SqlParameter("@Search", txtSearch.Text)
        para(14) = New SqlParameter("@Con", cboCon.SelectedIndex)
        dt = Da.ExecuteDataset(CommandType.StoredProcedure, "sp_SYS_CUSTOMER", para).Tables(0) 'เปลี่ยนเองเฟ้ย

        If dt.Rows.Count > 0 Then
            dgvDetails.DataSource = dt
            FormatHeader()
        Else
            dgvDetails.DataSource = Nothing
        End If
    End Sub
    Private Sub FormatHeader()
        With dgvDetails
            If .RowCount > 0 Then
                .Columns(0).HeaderText = "รหัส"
                .Columns(1).HeaderText = "พนักงาน"
                .Columns(2).HeaderText = "ประเภทลูกค้า"
                .Columns(3).HeaderText = "คำนำหน้า"
                .Columns(4).HeaderText = "ชื่อ"
                .Columns(5).HeaderText = "นามสกุุล"
                .Columns(6).HeaderText = "คำอธิบาย"
                .Columns(7).HeaderText = "สถานะ"



                .Columns(0).Width = 100
                .Columns(1).Width = 0
                .Columns(2).Width = 100
                .Columns(3).Width = 100
                .Columns(4).Width = 150
                .Columns(5).Width = 200
                .Columns(6).Width = 200
                .Columns(7).Width = 0

                .Columns(6).AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
                .Columns(1).Visible = False
                .Columns(7).Visible = False
            End If
        End With
    End Sub



    Private Sub dgvDetails_CellMouseUp(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles dgvDetails.CellMouseUp
        If e.RowIndex = -1 Then Exit Sub
        btnAdd.Enabled = False
        With dgvDetails
            txtCode.Text = .Rows.Item(e.RowIndex).Cells(0).Value.ToString()
            If CInt(.Rows.Item(e.RowIndex).Cells(2).Value.ToString) = 0 Then
                radPrivate.Checked = True
                radCorperate.Checked = False
            Else
                radPrivate.Checked = False
                radCorperate.Checked = True
            End If
            GetcomboTitle()
            cboCusType.Text = .Rows.Item(e.RowIndex).Cells(3).Value.ToString()
            txtName.Text = .Rows.Item(e.RowIndex).Cells(4).Value.ToString()
            txtName2.Text = .Rows.Item(e.RowIndex).Cells(5).Value.ToString()
            txtRemark.Text = .Rows.Item(e.RowIndex).Cells(6).Value.ToString()
            cboEmployee.SelectedValue = .Rows.Item(e.RowIndex).Cells(1).Value.ToString()
            'call Address And Contact
            GetCusAddress(txtCode.Text)
            GetContact(txtCode.Text)

            txtName.Focus()
            txtName.SelectAll()
        End With
    End Sub
    Private Sub GetCusAddress(ByVal cuscode As String)
        Dim dt As DataTable
        dgvAddress.AutoGenerateColumns = False
        Dim para(10) As SqlParameter

        para(0) = New SqlParameter("@ACTION", 1)
        para(1) = New SqlParameter("@Cust_Address_ID", 0)
        para(2) = New SqlParameter("@Customer_Code", cuscode)
        para(3) = New SqlParameter("@Cust_Address", "")
        para(4) = New SqlParameter("@Cust_Tel", 0)
        para(5) = New SqlParameter("@Cust_Fax", "")
        para(6) = New SqlParameter("@Cust_Mobile", "")
        para(7) = New SqlParameter("@Cust_Email", "")
        para(8) = New SqlParameter("@Cust_Type", 0)
        para(9) = New SqlParameter("@Cust_Billing", 0)

        dt = Da.ExecuteDataset(CommandType.StoredProcedure, "sp_SYS_CUSTADDRESS", para).Tables(0) 'เปลี่ยนเองเฟ้ย

        If dt.Rows.Count > 0 Then
            dgvAddress.DataSource = dt
        Else
            dgvAddress.DataSource = Nothing
        End If
    End Sub
    Private Sub GetContact(ByVal cuscode As String)
        Dim dt As DataTable
        dgvContact.AutoGenerateColumns = False
        Dim para(9) As SqlParameter
        para(0) = New SqlParameter("@ACTION", 1)
        para(1) = New SqlParameter("@Cust_ContactList_ID", 0)
        para(2) = New SqlParameter("@Customer_Code", cuscode)
        para(3) = New SqlParameter("@ust_ContactList_Name", "")
        para(4) = New SqlParameter("@Cust_ContactList_Address", 0)
        para(5) = New SqlParameter("@Cust_ContactList_Tel", "")
        para(6) = New SqlParameter("@Cust_ContactList_Mobile", "")
        para(7) = New SqlParameter("@Cust_ContactList_Email", "")
        para(8) = New SqlParameter("@Cust_ContactList_Status", 0)

        dt = Da.ExecuteDataset(CommandType.StoredProcedure, "sp_SYS_CONTACTLIST", para).Tables(0) 'เปลี่ยนเองเฟ้ย

        If dt.Rows.Count > 0 Then
            dgvContact.DataSource = dt

        Else
            dgvContact.DataSource = Nothing
        End If
    End Sub
    Private Sub GetcomboTitle()
        Dim dt As New DataTable
        dt.Columns.Add("ID")
        dt.Columns.Add("Name")
        If radPrivate.Checked Then

            dt.NewRow()
            dt.Rows.Add(0, "คุณ")
            dt.Rows.Add(1, "นาย")
            dt.Rows.Add(2, "นาง")
            dt.Rows.Add(3, "นางสาว")

        Else
            dt.NewRow()
            dt.Rows.Add(0, "บริษัท")
            dt.Rows.Add(1, "ห้างหุ้นส่วนจำกัด")
            dt.Rows.Add(2, "กลุ่ม")
            dt.Rows.Add(3, "หมู่บ้าน")

        End If
        cboCusType.DataSource = dt
        cboCusType.ValueMember = "ID"
        cboCusType.DisplayMember = "Name"

    End Sub

    Private Sub GetEmployee()
        Dim dt As DataTable
        Dim para(13) As SqlParameter

        para(0) = New SqlParameter("@ACTION", 3)
        para(1) = New SqlParameter("@Employee_Code", "")
        para(2) = New SqlParameter("@Department_ID", 0)
        para(3) = New SqlParameter("@Employee_Title", "")
        para(4) = New SqlParameter("@Employee_Fname", "")
        para(5) = New SqlParameter("@Employee_Lname", "")
        para(6) = New SqlParameter("@Employee_Position", "")
        para(7) = New SqlParameter("@Employee_Sale", 0)
        para(8) = New SqlParameter("@Employee_StatusAccount", 0)
        para(9) = New SqlParameter("@Employee_level", 0)
        para(10) = New SqlParameter("@Employee_Status", 0)
        para(11) = New SqlParameter("@Create_User", "")
        para(12) = New SqlParameter("@Update_User", "")
        dt = Da.ExecuteDataset(CommandType.StoredProcedure, "sp_SYS_EMPLOYEE", para).Tables(0)


        If dt.Rows.Count > 0 Then
            cboEmployee.DataSource = dt
            cboEmployee.BeginUpdate()
            cboEmployee.DisplayMember = "Employee_Fname"
            cboEmployee.ValueMember = "Employee_Code"
            cboEmployee.EndUpdate()

        Else
            cboEmployee.DataSource = Nothing
        End If
    End Sub
    Private Function Get_Code() As String
        Try
            Dim NewCode As String = ""
            Dim L_Trans_Runing As String = "00001"
            Dim paraCode(14) As SqlParameter
            paraCode(0) = New SqlParameter("@ACTION", 5)
            paraCode(1) = New SqlParameter("@Customer_Code", "")
            paraCode(2) = New SqlParameter("@Employee_Code", IIf(cboEmployee.SelectedIndex = -1, "", cboEmployee.SelectedValue))
            paraCode(3) = New SqlParameter("@Customer_Type", IIf(radPrivate.Checked, 0, 1))
            paraCode(4) = New SqlParameter("@Customer_Title", cboCusType.Text)
            paraCode(5) = New SqlParameter("@Customer_Name_1", txtName.Text)
            paraCode(6) = New SqlParameter("@Customer_Name_2", txtName2.Text)
            paraCode(7) = New SqlParameter("@Customer_Dest", txtRemark.Text)
            paraCode(8) = New SqlParameter("@Customer_Status", 0)
            paraCode(9) = New SqlParameter("@Create_User", FRAMEWORK.Framework.F_Username_Str)
            paraCode(10) = New SqlParameter("@Create_Date", "")
            paraCode(11) = New SqlParameter("@Update_User", FRAMEWORK.Framework.F_Username_Str)
            paraCode(12) = New SqlParameter("@Update_Date", 0)
            paraCode(13) = New SqlParameter("@Search", "")
            paraCode(14) = New SqlParameter("@Con", 0)
            Dim ds As DataTable = Da.ExecuteDataset(CommandType.StoredProcedure, "sp_SYS_CUSTOMER", paraCode).Tables(0)
            If ds.Rows.Count <= 0 Then
                NewCode = "CS-" & L_Trans_Runing
                'NewCode = Today.Year.ToString.Substring(2) & Today.Month.ToString("00") & L_Trans_Runing
            Else
                NewCode = ds.Rows(0).Item(0).ToString
                L_Trans_Runing = Format(Val(NewCode.Substring(3, 5)) + 1, "00000")
                NewCode = "CS-" & L_Trans_Runing
            End If
            'NewCode = ds.Rows(0).Item(0).ToString.Substring(3, 5)
            'Dim tmp1 As Integer = CInt(NewCode) + 1
            Return NewCode
        Catch ex As Exception
            MsgBox(ex.Message)
            Exit Function
        End Try
    End Function
    Private Sub Insert()
        If MessageBox.Show("คุณต้องการเพิ่มข้อมูล ใช่หรือไม่?", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = DialogResult.Yes Then
            Try
                Dim tmp_NewCode As String = Get_Code()
                Da.BeginTransaction()
                Dim para(14) As SqlParameter
                para(0) = New SqlParameter("@ACTION", 1)
                para(1) = New SqlParameter("@Customer_Code", "")
                ' ******************  เพิ่ม  ***************************** 
                para(2) = New SqlParameter("@Employee_Code", IIf(cboEmployee.SelectedIndex = -1, "", cboEmployee.SelectedValue))
                para(3) = New SqlParameter("@Customer_Type", IIf(radPrivate.Checked, 0, 1))
                para(4) = New SqlParameter("@Customer_Title", cboCusType.Text)
                para(5) = New SqlParameter("@Customer_Name_1", txtName.Text)
                para(6) = New SqlParameter("@Customer_Name_2", txtName2.Text)
                para(7) = New SqlParameter("@Customer_Dest", txtRemark.Text)
                para(8) = New SqlParameter("@Customer_Status", 0)
                para(9) = New SqlParameter("@Create_User", FRAMEWORK.Framework.F_Username_Str)
                para(10) = New SqlParameter("@Create_Date", "")
                para(11) = New SqlParameter("@Update_User", FRAMEWORK.Framework.F_Username_Str)
                para(12) = New SqlParameter("@Update_Date", 0)
                para(13) = New SqlParameter("@Search", "")
                para(14) = New SqlParameter("@Con", 0)

                Dim result As Integer
                result = Da.ExecuteNonQuery(Da.Transaction, CommandType.StoredProcedure, "sp_SYS_CUSTOMER", para) 'เปลี่ยนเองเฟ้ย

                '***************************************INSERT ADDRESS ***************************************
                For i As Integer = 0 To dgvAddress.Rows.Count - 2
                    Dim paraAddress(9) As SqlParameter
                    paraAddress(0) = New SqlParameter("@ACTION", 2)
                    paraAddress(1) = New SqlParameter("@Cust_Address_ID", 0)
                    paraAddress(2) = New SqlParameter("@Customer_Code", tmp_NewCode)
                    paraAddress(3) = New SqlParameter("@Cust_Address", dgvAddress.Rows.Item(i).Cells(1).FormattedValue.ToString)
                    paraAddress(4) = New SqlParameter("@Cust_Tel", dgvAddress.Rows.Item(i).Cells(2).FormattedValue.ToString)
                    paraAddress(5) = New SqlParameter("@Cust_Fax", dgvAddress.Rows.Item(i).Cells(3).FormattedValue.ToString)
                    paraAddress(6) = New SqlParameter("@Cust_Mobile", dgvAddress.Rows.Item(i).Cells(4).FormattedValue.ToString)
                    paraAddress(7) = New SqlParameter("@Cust_Email", dgvAddress.Rows.Item(i).Cells(5).FormattedValue.ToString)
                    If dgvAddress.Rows.Item(i).Cells(6).FormattedValue.ToString = "True" Then
                        paraAddress(8) = New SqlParameter("@Cust_Type", 1)
                    Else
                        paraAddress(8) = New SqlParameter("@Cust_Type", 0)
                    End If

                    If dgvAddress.Rows.Item(i).Cells(7).FormattedValue.ToString = "True" Then
                        paraAddress(9) = New SqlParameter("@Cust_Billing", 1)
                    Else
                        paraAddress(9) = New SqlParameter("@Cust_Billing", 0)
                    End If

                    result = Da.ExecuteNonQuery(Da.Transaction, CommandType.StoredProcedure, "sp_SYS_CUSTADDRESS", paraAddress)
                    'result = Da.ExecuteNonQuery(CommandType.StoredProcedure, "sp_SYS_CUSTADDRESS", paraAddress)
                Next

                ' ---------------- Contact ------------------------------------

                For i As Integer = 0 To dgvContact.Rows.Count - 2
                    Dim paraContact(10) As SqlParameter
                    If dgvContact.Rows.Item(i).Cells(0).FormattedValue.ToString = "" Then
                        paraContact(0) = New SqlParameter("@ACTION", 2) ' insert
                    Else
                        paraContact(0) = New SqlParameter("@ACTION", 3) ' update
                    End If

                    paraContact(1) = New SqlParameter("@Cust_ContactList_ID", dgvContact.Rows.Item(i).Cells(0).FormattedValue.ToString)
                    paraContact(2) = New SqlParameter("@Customer_Code", tmp_NewCode)
                    paraContact(3) = New SqlParameter("@ust_ContactList_Name", dgvContact.Rows.Item(i).Cells(1).FormattedValue.ToString)
                    paraContact(4) = New SqlParameter("@Cust_ContactList_Address", dgvContact.Rows.Item(i).Cells(2).FormattedValue.ToString)
                    paraContact(5) = New SqlParameter("@Cust_ContactList_Tel", dgvContact.Rows.Item(i).Cells(3).FormattedValue.ToString)
                    paraContact(6) = New SqlParameter("@Cust_ContactList_Mobile", dgvContact.Rows.Item(i).Cells(4).FormattedValue.ToString)
                    paraContact(7) = New SqlParameter("@Cust_ContactList_Email", dgvContact.Rows.Item(i).Cells(5).FormattedValue.ToString)
                    If dgvContact.Rows.Item(i).Cells(6).FormattedValue.ToString = "True" Then
                        paraContact(8) = New SqlParameter("@Cust_ContactList_Status", 1)
                    Else
                        paraContact(8) = New SqlParameter("@Cust_ContactList_Status", 0)
                    End If


                    result = Da.ExecuteNonQuery(Da.Transaction, CommandType.StoredProcedure, "sp_SYS_CONTACTLIST", paraContact)
                Next


                If result > 0 Then
                    Da.CommitTransaction()
                    MessageBox.Show("เพิ่มข้อมูลเรียบร้อยแล้ว !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    Call ClearData(Me, Me)
                    Call LoadCustomer()
                    Me.Refresh()
                End If
            Catch ex As Exception
                Da.RollBackTransaction()
                MessageBox.Show("เกิดข้อผิดพลาด เนื่องจาก " & ex.Message, "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Exit Sub
            End Try
        End If

    End Sub
    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        If txtName.Text = "" Then
            MessageBox.Show("กรุณาป้อนชื่อก่อน !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            txtName.Focus()
            Exit Sub
        End If
        If radCorperate.Checked = False And radPrivate.Checked = False Then
            MessageBox.Show("กรุณาเลือกประเภทลูกค้า !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            txtName.Focus()
            Exit Sub
        End If
        If Me.dgvAddress.RowCount - 1 < 1 Then
            MessageBox.Show("กรุณากรอกข้อมูลที่อยู่ลูกค้า !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            dgvAddress.Focus()
            Exit Sub
        End If
        If Me.dgvContact.RowCount - 1 < 1 Then
            MessageBox.Show("กรุณากรอกข้อมูลผู้ติดต่อ !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            dgvContact.Focus()
            Exit Sub
        End If
        Insert()


    End Sub
    Private Sub ClearData(ByVal parentCtr As Control, Optional ByVal ignoreControl As Control = Nothing)
        Dim ctr As Control
        For Each ctr In parentCtr.Controls
            If Not (ctr Is ignoreControl) Then
                If TypeOf ctr Is TextBox Then
                    ctr.Text = ""
                ElseIf TypeOf ctr Is ComboBox Then
                    DirectCast(ctr, ComboBox).SelectedIndex = -1
                ElseIf TypeOf ctr Is ListBox Then
                    CType(ctr, ListBox).Items.Clear()
                End If
            End If
            ClearData(ctr, ignoreControl)
        Next
        If dgvAddress.Rows.Count > 1 Then
            Dim dt As DataTable = DirectCast(dgvAddress.DataSource, DataTable)
            If dt Is Nothing Then
                dgvAddress.Rows.Clear()
            Else
                dt.Rows.Clear()
            End If
        End If
        If dgvContact.Rows.Count > 1 Then
            Dim dt As DataTable = DirectCast(dgvContact.DataSource, DataTable)
            If dt Is Nothing Then
                dgvContact.Rows.Clear()
            Else
                dt.Rows.Clear()
            End If
        End If
        btnAdd.Enabled = True
        'dgvContact.DataSource = Nothing
        'dgvAddress.DataSource = Nothing
    End Sub

    Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
        btnAdd.Enabled = True
        ClearData(Me, Me)
        GetEmployee()
        LoadCustomer()
    End Sub
    Private Sub Edit()
        If MessageBox.Show("คุณต้องการแก้ไขข้อมูล ใช่หรือไม่?", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = DialogResult.Yes Then
            Try
                Da.BeginTransaction()
                Dim para(14) As SqlParameter
                para(0) = New SqlParameter("@ACTION", 2)

                para(1) = New SqlParameter("@Customer_Code", txtCode.Text)
                ' ******************  เพิ่ม  ***************************** 
                para(2) = New SqlParameter("@Employee_Code", IIf(cboEmployee.SelectedIndex = -1, "", cboEmployee.SelectedValue))
                para(3) = New SqlParameter("@Customer_Type", IIf(radPrivate.Checked, 0, 1))
                para(4) = New SqlParameter("@Customer_Title", cboCusType.Text)
                para(5) = New SqlParameter("@Customer_Name_1", txtName.Text)
                para(6) = New SqlParameter("@Customer_Name_2", txtName2.Text)
                para(7) = New SqlParameter("@Customer_Dest", txtRemark.Text)
                para(8) = New SqlParameter("@Customer_Status", 0)
                para(9) = New SqlParameter("@Create_User", FRAMEWORK.Framework.F_Username_Str)
                para(10) = New SqlParameter("@Create_Date", DateTime.Now)
                para(11) = New SqlParameter("@Update_User", FRAMEWORK.Framework.F_Username_Str)
                para(12) = New SqlParameter("@Update_Date", DateTime.Now)
                para(13) = New SqlParameter("@Search", txtSearch.Text)
                para(14) = New SqlParameter("@Con", cboCon.SelectedIndex)
                Dim result As Integer
                result = Da.ExecuteNonQuery(Da.Transaction, CommandType.StoredProcedure, "sp_SYS_CUSTOMER", para)

                ' ---------------- Address ------------------------------------
                For i As Integer = 0 To dgvAddress.Rows.Count - 2
                    Dim paraAddress(10) As SqlParameter
                    If dgvAddress.Rows.Item(i).Cells(0).FormattedValue.ToString = "" Then
                        paraAddress(0) = New SqlParameter("@ACTION", 2) ' insert
                    Else
                        paraAddress(0) = New SqlParameter("@ACTION", 3) ' update
                    End If

                    paraAddress(1) = New SqlParameter("@Cust_Address_ID", dgvAddress.Rows.Item(i).Cells(0).FormattedValue.ToString)
                    paraAddress(2) = New SqlParameter("@Customer_Code", txtCode.Text)
                    paraAddress(3) = New SqlParameter("@Cust_Address", dgvAddress.Rows.Item(i).Cells(1).FormattedValue.ToString)
                    paraAddress(4) = New SqlParameter("@Cust_Tel", dgvAddress.Rows.Item(i).Cells(2).FormattedValue.ToString)
                    paraAddress(5) = New SqlParameter("@Cust_Fax", dgvAddress.Rows.Item(i).Cells(3).FormattedValue.ToString)
                    paraAddress(6) = New SqlParameter("@Cust_Mobile", dgvAddress.Rows.Item(i).Cells(4).FormattedValue.ToString)
                    paraAddress(7) = New SqlParameter("@Cust_Email", dgvAddress.Rows.Item(i).Cells(5).FormattedValue.ToString)
                    Dim Iscom As Integer
                    If dgvAddress.Rows.Item(i).Cells(6).FormattedValue.ToString = "True" Then
                        Iscom = 1
                    Else
                        Iscom = 0
                    End If
                    paraAddress(8) = New SqlParameter("@Cust_Type", Iscom)
                    Dim IsBill As Integer
                    If dgvAddress.Rows.Item(i).Cells(7).FormattedValue.ToString = "True" Then
                        IsBill = 1
                    Else
                        IsBill = 0
                    End If
                    paraAddress(9) = New SqlParameter("@Cust_Billing", IsBill)
                    result = Da.ExecuteNonQuery(Da.Transaction, CommandType.StoredProcedure, "sp_SYS_CUSTADDRESS", paraAddress)
                Next
                ' ---------------- Contact ------------------------------------

                For i As Integer = 0 To dgvContact.Rows.Count - 2
                    Dim paraContact(10) As SqlParameter
                    If dgvContact.Rows.Item(i).Cells(0).FormattedValue.ToString = "" Then
                        paraContact(0) = New SqlParameter("@ACTION", 2) ' insert
                    Else
                        paraContact(0) = New SqlParameter("@ACTION", 3) ' update
                    End If

                    paraContact(1) = New SqlParameter("@Cust_ContactList_ID", dgvContact.Rows.Item(i).Cells(0).FormattedValue.ToString)
                    paraContact(2) = New SqlParameter("@Customer_Code", txtCode.Text)
                    paraContact(3) = New SqlParameter("@ust_ContactList_Name", dgvContact.Rows.Item(i).Cells(1).FormattedValue.ToString)
                    paraContact(4) = New SqlParameter("@Cust_ContactList_Address", dgvContact.Rows.Item(i).Cells(2).FormattedValue.ToString)
                    paraContact(5) = New SqlParameter("@Cust_ContactList_Tel", dgvContact.Rows.Item(i).Cells(3).FormattedValue.ToString)
                    paraContact(6) = New SqlParameter("@Cust_ContactList_Mobile", dgvContact.Rows.Item(i).Cells(4).FormattedValue.ToString)
                    paraContact(7) = New SqlParameter("@Cust_ContactList_Email", dgvContact.Rows.Item(i).Cells(5).FormattedValue.ToString)
                    Dim Iscom As Integer
                    If dgvContact.Rows.Item(i).Cells(6).FormattedValue.ToString = "True" Then
                        Iscom = 1
                    Else
                        Iscom = 0
                    End If
                    paraContact(8) = New SqlParameter("@Cust_ContactList_Status", Iscom)

                    result = Da.ExecuteNonQuery(Da.Transaction, CommandType.StoredProcedure, "sp_SYS_CONTACTLIST", paraContact)
                Next


                If result > 0 Then
                    Da.CommitTransaction()
                    MessageBox.Show("แก้ไขข้อมูลเรียบร้อยแล้ว !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    Call ClearData(Me, Me)
                    Call LoadCustomer()
                End If
            Catch ex As Exception
                Da.RollBackTransaction()
                MessageBox.Show("เกิดข้อผิดพลาด เนื่องจาก " & ex.Message, "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Exit Sub
            End Try
        End If     
    End Sub
    Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
        If txtName.Text.Trim() = "" Then
            MessageBox.Show("กรุณาเลือกชื่อก่อน !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            txtName.Focus()
            Exit Sub
        End If
        If radCorperate.Checked = False And radPrivate.Checked = False Then
            MessageBox.Show("กรุณาเลือกประเภทลูกค้า !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            txtName.Focus()
            Exit Sub
        End If
        If Me.dgvAddress.RowCount - 1 < 1 Then
            MessageBox.Show("กรุณากรอกข้อมูลที่อยู่ลูกค้า !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            dgvAddress.Focus()
            Exit Sub
        End If
        If Me.dgvContact.RowCount - 1 < 1 Then
            MessageBox.Show("กรุณากรอกข้อมูลผู้ติดต่อ !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            dgvContact.Focus()
            Exit Sub
        End If

        Edit()

    End Sub

    Private Sub radPrivate_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles radPrivate.CheckedChanged
        GetcomboTitle()
    End Sub

    Private Sub radCorperate_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles radCorperate.CheckedChanged
        GetcomboTitle()
    End Sub

    Private Sub txtSearch_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtSearch.TextChanged
        Search()
    End Sub
    Private Sub Search()
        Dim dt As DataTable
        Dim para(14) As SqlParameter

        para(0) = New SqlParameter("@ACTION", 4)
        para(1) = New SqlParameter("@Customer_Code", "")
        para(2) = New SqlParameter("@Employee_Code", "")
        para(3) = New SqlParameter("@Customer_Type", 0)
        para(4) = New SqlParameter("@Customer_Title", "")
        para(5) = New SqlParameter("@Customer_Name_1", "")
        para(6) = New SqlParameter("@Customer_Name_2", "")
        para(7) = New SqlParameter("@Customer_Dest", "")
        para(8) = New SqlParameter("@Customer_Status", 0)
        para(9) = New SqlParameter("@Create_User", FRAMEWORK.Framework.F_Username_Str)
        para(10) = New SqlParameter("@Create_Date", "")
        para(11) = New SqlParameter("@Update_User", FRAMEWORK.Framework.F_Username_Str)
        para(12) = New SqlParameter("@Update_Date", 0)
        para(13) = New SqlParameter("@Search", txtSearch.Text)
        para(14) = New SqlParameter("@Con", cboCon.SelectedIndex)
        dt = Da.ExecuteDataset(CommandType.StoredProcedure, "sp_SYS_CUSTOMER", para).Tables(0) 'เปลี่ยนเองเฟ้ย

        If dt.Rows.Count > 0 Then
            dgvDetails.DataSource = dt
            FormatHeader()
        Else
            dgvDetails.DataSource = Nothing
        End If
    End Sub
  
    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub




    Private Sub dgvDetails_CellFormatting(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles dgvDetails.CellFormatting
        If e.RowIndex = -1 Then
            Exit Sub
        Else
            If dgvDetails.Columns(e.ColumnIndex).Name = "Customer_Type" Then
                If CInt(e.Value) = 0 Then
                    e.FormattingApplied = True
                    e.Value = "บุคคล"
                Else
                    e.FormattingApplied = True
                    e.Value = "บริษัท"
                End If
            End If
        End If
    End Sub

    Private Sub dgvContact_CellClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvContact.CellClick


        If e.RowIndex = -1 Then
            Exit Sub
        End If

        If (dgvContact.Rows(e.RowIndex).Cells(0).Value Is Nothing) Then
            CusContactIndex = ""
        Else
            CusContactIndex = dgvContact.Rows(e.RowIndex).Cells(0).Value.ToString

        End If
        'If e.ColumnIndex = 6 Then
        '    Dim tmp As Integer = 0
        '    For i = 0 To dgvContact.RowCount - 1
        '        'If CBool(dgvContact.Rows(i).Cells(6).Value) = True Then
        '        '    MessageBox.Show("เลือกผู้ติดต่อได้แค่รายเดียว !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        '        '    dgvContact.Rows(i + 1).Cells(6).Value = False
        '        '    Exit Sub
        '        'End If

        '        If IsDBNull(dgvContact.Rows(i).Cells(6).Value) Then
        '        ElseIf CBool(dgvContact.Rows(i).Cells(6).Value) = True Then
        '            tmp = tmp + 1 'andalso
        '        End If
        '    Next
        '    If tmp > 1 Then
        '        MessageBox.Show("เลือกผู้ติดต่อได้แค่รายเดียว !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        '        dgvContact.Item(6, e.RowIndex).Value = False
        '        dgvContact.EndEdit()
        '        Exit Sub
        '    End If
        'End If
        If e.ColumnIndex = 7 Then
            If dgvContact.RowCount - 1 < 1 OrElse dgvContact.SelectedCells(0).RowIndex = dgvContact.RowCount - 1 Then
                Exit Sub
            End If
            If MessageBox.Show("คุณต้องการลบข้อมูล ใช่หรือไม่?", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = DialogResult.Yes Then
                If (CusContactIndex = "") Then
                    dgvContact.Rows.Remove(dgvContact.CurrentRow)
                    dgvContact.Refresh()
                Else
                    DeleteContact(CInt(CusContactIndex))
                    GetContact(txtCode.Text)
                    dgvContact.RefreshEdit()
                End If
            Else
                Exit Sub
            End If

        End If
    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        If txtName.Text.Trim() = "" Then
            MessageBox.Show("กรุณาเลือกชื่อก่อน !!!", "ผลการตรวจสอบ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            txtName.Focus()
            Exit Sub
        End If
        Delete()
        
    End Sub

    Private Sub Delete()
        If MessageBox.Show("คุณต้องการลบข้อมูล ใช่หรือไม่?", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = DialogResult.Yes Then
            Dim para(14) As SqlParameter
            para(0) = New SqlParameter("@ACTION", 6)
            para(1) = New SqlParameter("@Customer_Code", txtCode.Text)
            para(2) = New SqlParameter("@Employee_Code", " ")
            para(3) = New SqlParameter("@Customer_Type", 0)
            para(4) = New SqlParameter("@Customer_Title", " ")
            para(5) = New SqlParameter("@Customer_Name_1", " ")
            para(6) = New SqlParameter("@Customer_Name_2", " ")
            para(7) = New SqlParameter("@Customer_Dest", " ")
            para(8) = New SqlParameter("@Customer_Status", 0)
            para(9) = New SqlParameter("@Create_User", " ")
            para(10) = New SqlParameter("@Create_Date", DateTime.Now())
            para(11) = New SqlParameter("@Update_User", " ")
            para(12) = New SqlParameter("@Update_Date", DateTime.Now())
            para(13) = New SqlParameter("@Search", txtSearch.Text)
            para(14) = New SqlParameter("@Con", cboCon.SelectedIndex)
            Dim result As Integer
            result = CInt(Da.ExecuteScalar(CommandType.StoredProcedure, "sp_SYS_CUSTOMER", para).ToString) 'เปลี่ยนเองเฟ้ย
            If result = 1 Then
                MessageBox.Show("ลบข้อมูลเรียบร้อยแล้ว !!!", "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Call ClearData(Me, Me)
                Call LoadCustomer()
            Else

            End If
        End If
    End Sub
    Private Sub DeleteAddress(ByVal index As Integer)
        Dim paraAddress(9) As SqlParameter
        paraAddress(0) = New SqlParameter("@ACTION", 4) ' delete
        paraAddress(1) = New SqlParameter("@Cust_Address_ID", index)
        paraAddress(2) = New SqlParameter("@Customer_Code", txtCode.Text)
        paraAddress(3) = New SqlParameter("@Cust_Address", " ")
        paraAddress(4) = New SqlParameter("@Cust_Tel", " ")
        paraAddress(5) = New SqlParameter("@Cust_Fax", " ")
        paraAddress(6) = New SqlParameter("@Cust_Mobile", " ")
        paraAddress(7) = New SqlParameter("@Cust_Email", " ")
        paraAddress(8) = New SqlParameter("@Cust_Type", 0)
        paraAddress(9) = New SqlParameter("@Cust_Billing", 0)
        Dim result As Integer
        result = CInt(Da.ExecuteScalar(CommandType.StoredProcedure, "sp_SYS_CUSTADDRESS", paraAddress).ToString)

    End Sub

    Private Sub DeleteContact(ByVal index As Integer)
        Dim paraContact(8) As SqlParameter

        paraContact(0) = New SqlParameter("@ACTION", 4) ' update
        paraContact(1) = New SqlParameter("@Cust_ContactList_ID", index)
        paraContact(2) = New SqlParameter("@Customer_Code", txtCode.Text)
        paraContact(3) = New SqlParameter("@ust_ContactList_Name", " ")
        paraContact(4) = New SqlParameter("@Cust_ContactList_Address", " ")
        paraContact(5) = New SqlParameter("@Cust_ContactList_Tel", " ")
        paraContact(6) = New SqlParameter("@Cust_ContactList_Mobile", " ")
        paraContact(7) = New SqlParameter("@Cust_ContactList_Email", " ")
        paraContact(8) = New SqlParameter("@Cust_ContactList_Status", 0)

        Dim result As String
        result = Da.ExecuteScalar(CommandType.StoredProcedure, "sp_SYS_CONTACTLIST", paraContact).ToString
    End Sub

    Private Sub dgvAddress_CellClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvAddress.CellClick

        If e.RowIndex = -1 Then
            Exit Sub
        End If
        If (dgvAddress.Rows(e.RowIndex).Cells(0).Value Is Nothing) Then
            CusAddressIndex = ""
        Else
            CusAddressIndex = dgvAddress.Rows(e.RowIndex).Cells(0).Value.ToString

        End If
        If e.ColumnIndex = 8 Then
            If dgvAddress.RowCount - 1 < 1 OrElse dgvAddress.SelectedCells(0).RowIndex = dgvAddress.RowCount - 1 Then
                Exit Sub
            End If

            If MessageBox.Show("คุณต้องการลบข้อมูล ใช่หรือไม่?", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = DialogResult.Yes Then
                If (CusAddressIndex = "") Then
                    dgvAddress.Rows.Remove(dgvAddress.CurrentRow)
                    dgvAddress.Refresh()
                Else
                    DeleteAddress(CInt(CusAddressIndex))
                    GetCusAddress(txtCode.Text)
                    dgvAddress.RefreshEdit()
                End If
            Else
                Exit Sub
            End If
        End If
    End Sub

    Private Sub cboCon_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboCon.SelectedIndexChanged
        txtSearch.Text = ""
        txtSearch.Focus()
    End Sub
End Class